import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
	
public class Connexion_BD {
	
	public Connexion_BD() {
	}
		
	// Méthodes pour la partie Compte
	
	   public static String getMdpCompte(String pseud) throws SQLException, ClassNotFoundException {
		   Connection con;
		   ResultSet resultat;
		   Statement stmt;
		   String mdp = null;
		   
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          resultat = stmt.executeQuery("SELECT mdp FROM utilisateur WHERE pseudo='"+pseud+"'");
	          while(resultat.next()) {
	    	mdp = resultat.getString(1);
	          }
	       } catch (SQLException e) {
	    	   System.out.println(e.getMessage());
		          System.out.println("Ce compte n'existe pas !");
		       }
 return mdp;

	}
	   
	   public static String getMail(String pseud) throws SQLException, ClassNotFoundException {
		   Connection con;
		   ResultSet resultat;
		   Statement stmt;
		   String mail = null;
		   
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          resultat = stmt.executeQuery("SELECT mail FROM utilisateur WHERE pseudo='"+pseud+"'");
	          while(resultat.next()) {
	    	mail = resultat.getString(1);
	          }
	       } catch (SQLException e) {
		          System.out.println("Ce compte n'existe pas !");
		       }
 return mail;

	}
	   
	   public static void EnregistrerCompte(String pseud, String mel, String pass) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "INSERT INTO utilisateur VALUES ('"+pseud+"','"+mel+"','"+pass+"')";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Ce pseudo est déjà utilisé dans la base !");
		       }
	}
	   
	   public static void SupprimerCompte(String nom) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM utilisateur WHERE pseudo='"+nom+"'";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Ce pseudo n'existe pas !");
		       }
	}
	   
	   
	   // Méthodes pour la partie Salons
	   
	   public static String getMdpSalon(String salon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   ResultSet resultat;
		   Statement stmt;
		   String mdp = null;
		   
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          resultat = stmt.executeQuery("SELECT mdpsalon FROM salon WHERE nomsalon='"+salon+"'");
	          while(resultat.next()) {
	    	mdp = resultat.getString(1);
	          }
	       } catch (SQLException e) {
		          System.out.println("Ce salon n'existe pas !");
		       }
 return mdp;

	}
	   
	   public static boolean estPublic(String salon) throws SQLException, ClassNotFoundException {
		   boolean res = true;
		   String mdp = getMdpSalon(salon);
		   
		   if (mdp.equals("")) 
		   {
			   res = true;
		   }
		   
		   else
		   {
			   res = false;
		   }
		   return res;
	}
	   
	   public static boolean verifExistenceSalon(String salon) throws SQLException, ClassNotFoundException {
		   // Renvoie true si le salon existe ou faux sinon.
		   Connection con;
		   Statement stmt;	
		   ResultSet res;
		   String req = null;
		   int nbSalon = 0;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         req = "SELECT count(*) AS nbLignes FROM salon WHERE nomsalon = '"+salon+"' ";
	         res = stmt.executeQuery(req);
			res.next();
			nbSalon = res.getInt("nbLignes");
			
			}
	      catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	      return (nbSalon!=0);
	   }
	   
	   public static String getDescription(String salon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   ResultSet resultat;
		   Statement stmt;
		   String description = null;
		   
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          resultat = stmt.executeQuery("SELECT description FROM salon WHERE nomsalon='"+salon+"'");
	          while(resultat.next()) {
	    	description = resultat.getString(1);
	          }
	       } catch (SQLException e) {
		          System.out.println("Ce salon n'existe pas !");
		       }
 return description;

	}
	   
	   public static int getNbPlaces(String salon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   ResultSet resultat;
		   Statement stmt;
		   int nbPlaces = 0;
		   
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          resultat = stmt.executeQuery("SELECT nbplaces FROM salon WHERE nomsalon='"+salon+"'");
	          while(resultat.next()) {
	    	nbPlaces = resultat.getInt(1);
	          }
	       } catch (SQLException e) {
		          System.out.println("Ce salon n'existe pas !");
		       }
 return nbPlaces;

	}
	   
	   public static void EnregistrerSalon(String nom, String mdp, String description, int nbplaces) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "INSERT INTO salon VALUES ('"+nom+"','"+mdp+"','"+description+"', "+nbplaces+")";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Ce salon existe déjà !");
		       }
	}
	   
	   public static void SupprimerSalon(String nom) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM salon WHERE nomsalon='"+nom+"'";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Ce salon n'existe pas !");
		       }
	}
	   
	   public static int NbSalons() throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   ResultSet res;
		   String req = null;
		   int nbSalons = 0;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         req = "SELECT count(*) AS nbLignes FROM salon";
	         res = stmt.executeQuery(req);
			res.next();
			nbSalons = res.getInt("nbLignes");
			
			}
	      catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	      return nbSalons;
	   }
	   
	   public static String[] getSalons() throws SQLException, ClassNotFoundException {
	 		int nbSalons = NbSalons();
	 		Connection con;
	 		Statement stmt;	
	 		ResultSet resultat;
	 		String [] Tableau = null;
	 		
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         resultat = stmt.executeQuery("SELECT nomsalon FROM salon");

	         Tableau = new String[nbSalons];
	     
	     			while(resultat.next()) {
	 
	     				for (int i = 0; i < nbSalons; i++)
	     				{
		 
	     					Tableau[i]= resultat.getString(1);
	     					resultat.next();
	     				}	
	     			}	
	      }
	      catch (SQLException e) {
	      System.out.println("Erreur ! ");
	       }
	 return Tableau;
}
	   
	   // Méthodes pour les bans
	   
	   public static void EnregistrerBan(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "INSERT INTO estbanni VALUES ('"+nomUtilisateur+"','"+nomSalon+"')";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur dans le nom de l'utilisateur ou le nom du salon !");
		       }
	}
	   
	   public static void SupprimerBan(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM estbanni WHERE nomsalon='"+nomSalon+"' AND pseudo='"+nomUtilisateur+"' ";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	}
	   
	   // Méthode pour les Admins
	   
	   public static void EnregistrerAdmin(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "INSERT INTO estadmin VALUES ('"+nomUtilisateur+"','"+nomSalon+"')";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur dans le nom de l'utilisateur ou le nom du salon !");
		       }
	}
	   
	   public static void SupprimerAdmin(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM estadmin WHERE nomsalon='"+nomSalon+"' AND pseudo='"+nomUtilisateur+"' ";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	}
	   
	   // Méthode pour connaître le nombre d'admins pour un salon donnée en paramètre
	   
	   public static int NbAdmins(String salon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   ResultSet res;
		   String req = null;
		   int nbAdmins = 0;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         req = "SELECT count(*) AS nbLignes FROM estadmin WHERE nomsalon = '"+salon+"' ";
	         res = stmt.executeQuery(req);
			res.next();
			nbAdmins = res.getInt("nbLignes");
			
			}
	      catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	      return nbAdmins;
	   }
	   
	   // Méthode pour connaître tous les admins d'un salon
	   
	   public static String[] getAdmins(String salon) throws SQLException, ClassNotFoundException {
		 		int nbAdmins = NbAdmins(salon);
		 		Connection con;
		 		Statement stmt;	
		 		ResultSet resultat;
		 		String [] Tableau = null;
		 		
		      try {
		         Class.forName("org.postgresql.Driver");
		         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
		         stmt = con.createStatement();
		         resultat = stmt.executeQuery("SELECT pseudo FROM estadmin WHERE nomsalon = '"+salon+"'");
     
		         Tableau = new String[nbAdmins];
		     
		     			while(resultat.next()) {
		 
		     				for (int i = 0; i < nbAdmins; i++)
		     				{
			 
		     					Tableau[i]= resultat.getString(1);
		     					resultat.next();
		     				}	
		     			}	
		      }
		      catch (SQLException e) {
		      System.out.println("Erreur ! ");
		       }
		 return Tableau;
	 }
	   
	   // Méthode pour savoir si un utilisateur est admin d'un salon
	   
	   public static boolean estAdmin(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   // Renvoie vrai si nomUtilisateur est admin de nomSalon ou faux sinon
		   
		   boolean bool = false;
		   String[] admins = getAdmins(nomSalon);
		   
		   		for (int i = 0; i < admins.length; i++) {
		   			if (nomUtilisateur.equals(admins[i]))
		   					{ bool = true;
		   					}
		   			}
		   		return bool;
		   		}  
	   
	   // Méthode pour vérifier si le mot de passe tappé par un utilisateur est correct
	   
	   public static boolean verifMdp(String pseudo, String mdp) throws SQLException, ClassNotFoundException {
		   // Renvoie vrai si mdp est le mot de passe de pseudo ou faux sinon
		   String bon_mdp = getMdpCompte(pseudo);
		   boolean bool = false;
		   if (mdp.equals(bon_mdp))
		   {
			   bool = true;
		   }
		   
		   return bool;
	   }
	   
	   // Méthode pour connaître le nombre de bannis pour un salon donnée en paramètre
	   
	   public static int NbBannis(String salon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   ResultSet res;
		   String req = null;
		   int nbBannis = 0;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         req = "SELECT count(*) AS nbLignes FROM estbanni WHERE nomsalon = '"+salon+"' ";
	         res = stmt.executeQuery(req);
			res.next();
			nbBannis = res.getInt("nbLignes");
			
			}
	      catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	      return nbBannis;
	   }
	   
	   // Méthode pour connaître tous les bannis d'un salon
	   
	   public static String[] getBannis(String salon) throws SQLException, ClassNotFoundException {
		 		int nbBannis = NbBannis(salon);
		 		Connection con;
		 		Statement stmt;	
		 		ResultSet resultat;
		 		String [] Tableau = null;
		 		
		      try {
		         Class.forName("org.postgresql.Driver");
		         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
		         stmt = con.createStatement();
		         resultat = stmt.executeQuery("SELECT pseudo FROM estbanni WHERE nomsalon = '"+salon+"'");
     
		         Tableau = new String[nbBannis];
		     
		     			while(resultat.next()) {
		 
		     				for (int i = 0; i < nbBannis; i++)
		     				{
			 
		     					Tableau[i]= resultat.getString(1);
		     					resultat.next();
		     				}	
		     			}	
		      }
		      catch (SQLException e) {
		      System.out.println("Erreur ! ");
		       }
		 return Tableau;
	 }
	   
	   // Méthode pour savoir si un utilisateur est banni d'un salon
	   
	   public static boolean estBanni(String nomUtilisateur, String nomSalon) throws SQLException, ClassNotFoundException {
		   // Renvoie vrai si nomUtilisateur est admin de nomSalon ou faux sinon
		   
		   boolean bool = false;
		   String[] bannis = getBannis(nomSalon);
		   
		   		for (int i = 0; i < bannis.length; i++) {
		   			if (nomUtilisateur.equals(bannis[i]))
		   					{ bool = true;
		   					}
		   			}
		   		return bool;
		   		}

	public static boolean verifExistenceCompte(String pseudo) throws ClassNotFoundException {
		   // Renvoie true si le compte existe ou faux sinon.
		   Connection con;
		   Statement stmt;	
		   ResultSet res;
		   String req = null;
		   int nbCompte = 0;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	         stmt = con.createStatement();
	         req = "SELECT count(*) AS nbLignes FROM utilisateur WHERE pseudo = '"+pseudo+"' ";
	         res = stmt.executeQuery(req);
			res.next();
			nbCompte = res.getInt("nbLignes");
			
			}
	      catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	      return (nbCompte!=0);
	   } 
	
	   public static void SupprimerBannisSalon(String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM estbanni WHERE nomsalon='"+nomSalon+"' ";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	}
	   
	   public static void SupprimerAdminsSalon(String nomSalon) throws SQLException, ClassNotFoundException {
		   Connection con;
		   Statement stmt;	
		   String req = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         con = DriverManager.getConnection("jdbc:postgresql://postgres-info/bdbaret","userbaret","pbaret");
	          stmt = con.createStatement();
	          req = "DELETE FROM estadmin WHERE nomsalon='"+nomSalon+"' ";
	          stmt.executeUpdate(req);
	       } catch (SQLException e) {
		          System.out.println("Erreur ! ");
		       }
	}
	   
	   public static boolean verifMdpSalon(String nomSalon, String mdp) throws SQLException, ClassNotFoundException {
		   boolean res = false;
		   String bon_mdp = Connexion_BD.getMdpSalon(nomSalon);
		   
		   if (mdp.equals(bon_mdp))
		   {
			   res = true;
		   }
		   
		   else 
		   {
			   res = false;
		   }
		   
		   return res; 
	   }
	   
}


